package com.ced.sip.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


/**
 * 
 * @author lgl
 * 
 */
public class ExcelUtil{
	
	/**
     * 导出excel
     * @param targetfile 保存路径及文件名
     * @param list 可以是多个sheet=====每个sheet包含三个键值：1、worksheet 工作表名     2、titleList 标题集合   3、valueList 数据集合
     * @author lgl 
     */
	public void expCommonExcel(String targetfile,List<Map<String,Object>> list){
		try {
			String worksheet="";
			Map<String,Object> map=new HashMap<String,Object>();
			List titleList=new ArrayList();
			List<Object[]> valueList=new ArrayList<Object[]>();
			//创建工作薄
			WritableWorkbook workbook = Workbook.createWorkbook(new File(targetfile));
			
			//创建单元格
			Label label;
			for(int k=0;k<list.size();k++)
			{   
				map=list.get(k);
				worksheet=(String)map.get("worksheet");
				titleList=(List)map.get("titleList");
				valueList=(List<Object[]>)map.get("valueList");
				
				//创建工作表
				WritableSheet sheet = workbook.createSheet(worksheet, k);
				
				//填充标题
				for(int i=0; i<titleList.size(); i++){
					label = new Label(i, 0, titleList.get(i).toString());
					 WritableFont font1 = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.WHITE);  
					//设置标题内容居中
					WritableCellFormat cellFormat = new WritableCellFormat(font1);
					cellFormat.setAlignment(Alignment.CENTRE);
					cellFormat.setBackground(Colour.GREY_50_PERCENT);
					cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);  
					 
					label.setCellFormat(cellFormat);
					//添加标题内容
					sheet.addCell(label);
					//设置自适应题列宽
					byte[] colength = titleList.get(i).toString().getBytes();
					sheet.setColumnView(i, colength.length+12);
					
					//设置列名行高
					sheet.setRowView(0, 500, false); 
				}
				//填充数据 行数
				for(int i=0; i<valueList.size(); i++){
					WritableCellFormat cellFormat2 = new WritableCellFormat();
					cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);  
					
					//列数
					for(int j=0; j< titleList.size(); j++){
						if(StringUtil.isNotBlank(valueList.get(i)[j])){
							
							label = new Label(j, i+1, valueList.get(i)[j].toString(),cellFormat2);
							sheet.addCell(label);
							
						}else{
							label = new Label(j, i+1, "",cellFormat2);
							sheet.addCell(label);	
						}
						
					}
					
				}
			}

			workbook.write();
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 读取excel中所有信息
	 * @param filePath 文件路径
	 * @author ted 2016-06-08
	 * @return 二维数组集
	 * @throws FileNotFoundException 
	 */
	public String[][] readExcel(String filePath)  {
		InputStream is = null ;
		try{
			is = new FileInputStream(filePath);
		}catch( FileNotFoundException fnfe ){
			System.out.print(" 文件未找到！") ;
			fnfe.printStackTrace() ;
		}
		return readExcel( is ) ;
	}
	/**
	 * 读取excel中所有信息
	 * @param is 文件输入流
	 * @author ted 2016-06-08
	 * @return 所的信息以二维数组集返回
	 */
	public String[][] readExcel(InputStream is) {
		String[][] strTemp = null;
		try {
			Workbook wb = Workbook.getWorkbook(is);
			Sheet rs = wb.getSheet(0);
			int rows = rs.getRows();
			int cols = rs.getColumns();

			strTemp = new String[rows][cols];
			for (int i = 0; i < rows; i++) {
				for (int j = 0; j < cols; j++) {
					Cell c = rs.getCell(j, i);
					strTemp[i][j] = c.getContents().trim();
				}
			}
			wb.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return strTemp;
	}
}
